package dai.samples.jpa.repository;

import dai.samples.jpa.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
 *
 * @author daify
 * @date 2019-07-30 16:32
 **/
public interface UserRepository
        extends JpaRepository <User, Long> {

    /**
     * 此时使用HQL查询，使用的实体名和实体属性名
     * @param id
     * @return
     */
    @Query(value = "from User where id =:id")
    User selectUserById(@Param("id") Long id);

    @Query(value = "from User where id =?1 and name =?2")
    User selectUserByIdAndName(@Param("id") Long id, @Param("name") String name);

    
    /**
     * 此时使用SQL查询，使用的表名称和列名
     * @param id
     * @return
     */
    @Query(value = "select * from cascade_user where id =:id",nativeQuery = true)
    User selectUserByIdWithSql(@Param("id") Long id);

    @Query(value = "select * from cascade_user where id =?1 and name =?2",nativeQuery = true)
    User selectUserByIdAndNameWithSql(@Param("id") Long id, @Param("name") String name);


    @Query(value = "select u.id,u.name,u.age,u.point," 
                   + "c.id as 'cart_id',c.count,c.total_money,c.update_time " 
                   + "from cascade_user u " 
                   + "left join cascade_user_cart c " 
                   + "on u.id = c.user_id " 
                   + "where u.id = :id",nativeQuery = true)
    Object[] selectUserCartInfoById(@Param("id") Long id);
    
}